package cn.lanqiao.dao.impl;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.swing.JTable;
import javax.swing.table.TableModel;
import cn.lanqiao.dao.UserDao;
import cn.lanqiao.entity.Student;
import cn.lanqiao.util.JdbcUtil;
import cn.lanqiao.entity.Users;
import java.util.Random;

public class UserDaoImpl implements UserDao {

	@Override
	public Object[][] getAllstudents() {
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int count = this.count();
		Object[][] datas = new Object[count][];
		String sql = "select * from edu_student";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			for (int i = 0; rs.next(); i++) {
				datas[i] = new Object[] { rs.getString("sid"), rs.getString("sname"), rs.getString("sgender"),
						rs.getString("scollege"), rs.getString("smajor"), rs.getString("sclass"),
						rs.getString("senrol") };
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(rs, ps, conn);
		}
		return datas;
	}

	@Override
	public int count() {
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select count(*) from edu_student";
		int num = 0;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				num = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(rs, ps, conn);
		}
		return num;
	}

	@Override
	public void deleteStu(String id) {
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String DeleteSql = "delete from edu_student where sid=?";
		try {
			ps = conn.prepareStatement(DeleteSql);
			ps.setString(1, id);
			rs = ps.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(rs, ps, conn);
		}
	}

	@Override
	public void addStudent(Student student) {
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement ps = null;
		String AddSql = "insert into edu_student values (?,?,?,?,?,?,?,?)";
		try {
			ps = conn.prepareStatement(AddSql);
			ps.setString(1, student.getUuid());
			ps.setString(2, student.getSid());
			ps.setString(3, student.getSname());
			ps.setString(4, student.getSgender());
			ps.setString(5, student.getScollege());
			ps.setString(6, student.getSmajor());
			ps.setString(7, student.getSclass());
			ps.setString(8, student.getSenrol());
			int rs = ps.executeUpdate(); // 0\1\2...结果
			if (rs > 0) {
				System.out.println("添加成功");
			} else {
				System.out.println("添加失败");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(null, ps, conn);
		}
	}

	@Override
	public void updateStudent(String id, Student student) {
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement ps = null;
		String UpdateSql = "update edu_student set sname=?,sgender=?,scollege=?,smajor=?,sclass=? where sid =?";
		try {
			ps = conn.prepareStatement(UpdateSql);
			ps.setString(1, student.getSname());
			ps.setString(2, student.getSgender());
			ps.setString(3, student.getScollege());
			ps.setString(4, student.getSmajor());
			ps.setString(5, student.getSclass());
			ps.setString(6, id);
			int rs = ps.executeUpdate(); // 0\1\2...结果
			if (rs > 0) {
				System.out.println("修改成功");
			} else {
				System.out.println("修改失败");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(null, ps, conn);
		}
	}

	@Override
	public void exportTable(JTable table, File file) throws IOException {
		TableModel model = table.getModel();
		FileWriter out = new FileWriter(file);

		for (int i = 0; i < model.getColumnCount(); i++) {
			out.write(model.getColumnName(i) + "\t");
		}
		out.write("\n");
		for (int i = 0; i < model.getRowCount(); i++) {
			for (int j = 0; j < model.getColumnCount(); j++) {
				out.write(model.getValueAt(i, j).toString() + "\t");
			}
			out.write("\n");
		}
		out.close();
		System.out.println("文件保存: " + file);
	}

	// 根据账号获取登录信息
	@Override
	public Users getUserByLoginId(String account) {
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Users user = null;
		String sql = "select * from edu_users where account =?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, account);
			rs = ps.executeQuery();
			if (rs.next()) {
				user = new Users(rs.getString("uuid"), rs.getString("account"), rs.getString("password"),
						rs.getString("telephone"), rs.getString("role"));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(rs, ps, conn);
		}
		return user;
	}

	// 根据手机号码获取信息
	@Override
	public Users getUserByTelephone(String telephone) {
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Users user = null;
		String sql = "select * from edu_users where telephone =?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, telephone);
			rs = ps.executeQuery();
			if (rs.next()) {
				user = new Users(rs.getString("uuid"), rs.getString("account"), rs.getString("password"),
						rs.getString("telephone"), rs.getString("role"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(rs, ps, conn);
		}
		return user;
	}

	// 发送6位数字验证码
	@Override
	public String sendVerification() {
		String verifyCode = String.valueOf(new Random().nextInt(899999) + 100000);
		return verifyCode;
	}

	// 根据手机号码修改密码
	@Override
	public int updatePassword(String telephone, String newPassword) {
		Connection conn = JdbcUtil.getConnection();
		int updateON = 0;
		PreparedStatement ps = null;
		String updateSql = "update edu_users set password=? where telephone=?";
		try {
			ps = conn.prepareStatement(updateSql);
			ps.setString(1, newPassword);
			ps.setString(2, telephone);
			int affter = ps.executeUpdate();
			if (affter > 0) {
				updateON = affter;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(null, ps, conn);
		}
		return updateON;
	}

	@Override
	public Object[][] getAllScores() {
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int count = this.sum();
		Object[][] datas = new Object[count][];
		String sql = "select s.sid,sname,cname,sgrade from edu_score s,edu_course c,edu_student t where s.sid=t.sid and s.scourse=c.cid";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			for (int i = 0; rs.next(); i++) {
				datas[i] = new Object[] { rs.getInt("sid"), rs.getString("sname"), rs.getString("cname"),
						rs.getString("sgrade") };
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(rs, ps, conn);
		}
		return datas;
	}

	@Override
	public int sum() {
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select count(sgrade) from edu_score s,edu_course c,edu_student t where s.sid=t.sid and s.scourse=c.cid";
		int num = 0;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				num = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(rs, ps, conn);
		}
		return num;
	}

	@Override
	public Student getStudentinformation(String sid) {
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Student student = null;
		String searchSql = "select * from edu_student where sid=?";
		try {
			ps = conn.prepareStatement(searchSql);
			ps.setString(1, sid);
			rs = ps.executeQuery();
			if (rs.next()) {

				student = new Student(null, null, rs.getString("sname"), rs.getString("sgender"),
						rs.getString("scollege"), rs.getString("smajor"), rs.getString("sclass"),
						rs.getString("senrol"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(rs, ps, conn);
		}
		return student;
	}

	@Override
	public Object[][] getAllUsers() {
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int count = this.sum();
		Object[][] datas = new Object[count][];
		String getAllUsers = "select * from edu_users";
		try {
			ps = conn.prepareStatement(getAllUsers);
			rs = ps.executeQuery();
			for (int i = 0; rs.next(); i++) {
				datas[i] = new Object[] { rs.getInt("account"), rs.getString("password"), rs.getString("telephone"),
						rs.getString("role") };
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(rs, ps, conn);
		}
		return datas;
	}

	@Override
	public int getNum() {
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select count(*) from edu_users";
		int num = 0;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				num = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(rs, ps, conn);
		}
		return num;
	}

	@Override
	public void deleteUser(String sid) {
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String DeleteSql = "delete from edu_users where account=?";
		try {
			ps = conn.prepareStatement(DeleteSql);
			ps.setString(1, sid);
			rs = ps.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(rs, ps, conn);
		}
	}

	@Override
	public void addUser(Users users) {
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement ps = null;
		String AddSql = "insert into edu_users values (?,?,?,?,?)";
		try {
			ps = conn.prepareStatement(AddSql);
			ps.setString(1, users.getUuid());
			ps.setString(3, users.getPassword());
			ps.setString(2, users.getAccount());
			ps.setString(4, users.getTelephone());
			ps.setString(5, users.getRole());
			int rs = ps.executeUpdate(); // 0\1\2...结果
			if (rs > 0) {
				System.out.println("添加成功");
			} else {
				System.out.println("添加失败");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(null, ps, conn);
		}
	}

	@Override
	public void updateUser(String account, Users user) {
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement ps = null;
		String UpdateSql = "update edu_users set password=?,telephone=? where account =?";
		try {
			ps = conn.prepareStatement(UpdateSql);
			ps.setString(1, user.getPassword());
			ps.setString(2, user.getTelephone());
			ps.setString(3, account);
			int rs = ps.executeUpdate(); // 0\1\2...结果
			if (rs > 0) {
				System.out.println("修改成功");
			} else {
				System.out.println("修改失败");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(null, ps, conn);
		}
	}
}
